package com.giscloud.generator.service;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.giscloud.commons.utils.ListPagerUtils;
import com.giscloud.commons.utils.StringUtils;
import com.giscloud.generator.dao.DatabaseDao;
import com.giscloud.generator.entity.DatabaseEntity;
import com.giscloud.generator.entity.Table;
import com.giscloud.generator.entity.TableEntity;
import com.giscloud.generator.utils.BusinessException;
import com.giscloud.generator.utils.GenUtils;
import com.giscloud.generator.utils.PageUtils;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.io.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.zip.ZipOutputStream;

/**
 * 代码生成器
 * 
 * @author giscloud
 */
@Service
@Log4j2
public class GeneratorService {
	@Autowired
	private DatabaseDao databaseDao;


	/**
	 * 分页查询
	 * @param tableName
	 * @param databaseId
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	public PageUtils queryList(String tableName, Long databaseId, int pageNum, int pageSize) {
		DatabaseEntity dbConfig = null;
		List<TableEntity> tableList = Lists.newArrayListWithExpectedSize(16);
		Map param = Maps.newHashMapWithExpectedSize(1);
		param.put("id",databaseId);
		List<DatabaseEntity> databases = databaseDao.listDatabases(param);
		if(CollectionUtil.isNotEmpty(databases) && databases.size() == 1){
			dbConfig = databases.get(0);
		}
		Connection conn = getConnection(dbConfig);
		try {
			// 执行sql语句,获取表名和表注释，在information_schema库下的表tables中的table_name为表名、TABLE_COMMENT为表注释信息
			String strSql = "";
			if (dbConfig.getDbType().equals("MySQL")) {
				strSql = "select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables where table_schema='" + dbConfig.getDbSchema() + "'";
				if (StringUtils.isNotBlank(tableName)) {
					strSql += " AND table_name LIKE ?";
				}
			} else if (dbConfig.getDbType().equals("PostgreSQL")) {
				strSql = "select t1.tablename as tableName,'' engine, obj_description(relfilenode, 'pg_class') as tableComment, now() as createTime from pg_tables t1, pg_class t2,pg_namespace t3 " +
						"where t1.schemaname ='" + dbConfig.getDbSchema() + "' and t1.tablename not like 'pg%' and t1.tablename not like 'sql_%' and t1.tablename = t2.relname and t1.schemaname = t3.nspname and t3.oid = t2.relnamespace ";
				if (StringUtils.isNotBlank(tableName)) {
					strSql += " AND t1.table_name LIKE ?";
				}
			} else if (dbConfig.getDbType().equals("Oracle")) {
				strSql = "select dt.table_name tableName, '' engine," +
						"dtc.comments tableComment," +
						"uo.created createTime" +
						"from user_tables dt," +
						"user_tab_comments dtc," +
						"user_objects uo " +
						"where dt.table_name = dtc.table_name and dt.table_name = uo.object_name and uo.object_type='TABLE'";
				if (StringUtils.isNotBlank(tableName)) {
					strSql += " AND dt.table_name LIKE ?";
				}
			} else if (dbConfig.getDbType().equals("SQLServer")) {
				strSql = "select * from" +
						"(select cast(so.name as varchar(500)) as tableName,'mssql' as engine, cast(sep.value as varchar(500)) as tableComment, getDate() as createTime " +
						"from sysobjects so " +
						"left JOIN sys.extended_properties sep " +
						"on sep.major_id=so.id and sep.minor_id=0 " +
						"where (xtype='U' or xtype='v') " +
						") t where 1=1";
				if (StringUtils.isNotBlank(tableName)) {
					strSql += " AND t.table_name LIKE ?";
				}
			}
			log.info("数据库表sql查询语句：{}" , strSql);
			// 获得sql的预编译对象
			PreparedStatement stmt = conn.prepareStatement(strSql);
			if (StringUtils.isNotBlank(tableName)) {
				if (dbConfig.getDbType().equals("Oracle")) {
					stmt.setString(1, "'%" + tableName.toUpperCase() + "%'");
				}else{
					stmt.setString(1, "'%" + tableName + "%'");
				}
			}
			// 执行查询
			ResultSet rs = stmt.executeQuery();
			// 展开结果集数据库
			while (rs.next()) {
				TableEntity table = new TableEntity();
				table.setTableName(rs.getString(1));
				table.setEngine(rs.getString(2));
				table.setComments(rs.getString(3));
				table.setCreateTime(rs.getDate(4));
				tableList.add(table);
			}
			if(rs != null){
				rs.close();
			}
			// 释放资源(注意：关闭资源顺序 先打开后关闭) 完成 后关闭
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					log.error("关闭Statement异常：{}", e);
				}
			}
		} catch (SQLException e) {
			throw new BusinessException("execute sql occer error", e);
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				throw new BusinessException("关闭Connection异常：{}", e);
			}
		}
		if (CollectionUtil.isNotEmpty(tableList)) {
			ListPagerUtils<TableEntity> tableEntityList = ListPagerUtils.create(tableList, pageSize);
			return new PageUtils(tableEntityList.getPagedList(pageNum), tableList.size(), pageSize, pageNum);

		}
		return null;
	}

	public List<DatabaseEntity> listDatabases(Map<String, Object> params) {
			//PageHelper.offsetPage(Integer.parseInt(params.get("pageNum").toString()), Integer.parseInt(params.get("pageSize").toString()));
		List<DatabaseEntity> databases = databaseDao.listDatabases(params);
		return databases;
	}
	/**
	 * 查询所有表，不分页
	 * @param tableName
	 * @param databases
	 * @return
	 */
	public List<Table> listAll(String tableName, List<DatabaseEntity> databases) {
		List<Table> tableList = Lists.newArrayListWithExpectedSize(16);
		int id = 10000;
		int pid = 0;
		Connection conn = null;
		try {
			for (DatabaseEntity dbConfig : databases) {
				Table table = new Table();
				table.setPid(pid);
				table.setId(dbConfig.getId());
				table.setTableName(dbConfig.getName());
				table.setEngine("");
				table.setComments("");
				table.setCreateTime(DateUtil.format(dbConfig.getCreateDt(), "yyyy-MM-dd"));
				tableList.add(table);
				conn = getConnection(dbConfig);
				// 执行sql语句,获取表名和表注释，在information_schema库下的表tables中的table_name为表名、TABLE_COMMENT为表注释信息
				String strSql = "";
				if (dbConfig.getDbType().equals("MySQL")) {
					strSql = "select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables where table_schema=?";
					if (StringUtils.isNotBlank(tableName)) {
						strSql += " AND table_name LIKE ?";
					}
				} else if (dbConfig.getDbType().equals("PostgreSQL")) {
					strSql = "select t1.tablename as tableName,'' engine, obj_description(relfilenode, 'pg_class') as tableComment, now() as createTime from pg_tables t1, pg_class t2,pg_namespace t3 " +
							 "where t1.schemaname =? and t1.tablename not like 'pg%' and t1.tablename not like 'sql_%' and t1.tablename = t2.relname and t1.schemaname = t3.nspname and t3.oid = t2.relnamespace ";
					if (StringUtils.isNotBlank(tableName)) {
						strSql += " AND t1.tablename LIKE ?";
					}
				} else if (dbConfig.getDbType().equals("Oracle")) {
					strSql = "select dt.table_name tableName, '' engine," +
							"dtc.comments tableComment," +
							"uo.created createTime " +
							"from user_tables dt," +
							"user_tab_comments dtc," +
							"user_objects uo " +
							"where dt.table_name = dtc.table_name and dt.table_name = uo.object_name and uo.object_type='TABLE'";
					if (StringUtils.isNotBlank(tableName)) {
						strSql += " AND dt.table_name LIKE ?";
					}
				} else if (dbConfig.getDbType().equals("SQLServer")) {
					strSql = "select * from" +
							"(select cast(so.name as varchar(500)) as tableName,'mssql' as engine, cast(sep.value as varchar(500)) as tableComment, getDate() as createTime " +
							"from sysobjects so " +
							"left JOIN sys.extended_properties sep " +
							"on sep.major_id=so.id and sep.minor_id=0 " +
							"where (xtype='U' or xtype='v') " +
							") t where 1=1";
					if (StringUtils.isNotBlank(tableName)) {
						strSql += " AND t.table_name LIKE ?";
					}
				}
				log.info("数据库表sql查询语句：{}", strSql);
				// 获得sql的预编译对象
				PreparedStatement stmt = conn.prepareStatement(strSql);
				if (StringUtils.isNotBlank(tableName)) {
					if (dbConfig.getDbType().equals("Oracle")) {
						stmt.setString(1, "'%" + tableName.toUpperCase() + "%'");
					}else{
						stmt.setString(2, "'%" + tableName + "%'");
					}
				}
				if(dbConfig.getDbType().equals("MySQL") || dbConfig.getDbType().equals("PostgreSQL")) {
					stmt.setString(1, dbConfig.getDbSchema());
				}
				// 执行查询
				ResultSet rs = stmt.executeQuery();
				// 展开结果集数据库
				while (rs.next()) {
					id++;
					table = new Table();
					table.setPid(dbConfig.getId());
					table.setId(id);
					table.setTableName(rs.getString(1));
					table.setEngine(StringUtils.isBlank(rs.getString(2)) ? "" : rs.getString(2));
					table.setComments(StringUtils.isBlank(rs.getString(3)) ? "" : rs.getString(3));
					table.setCreateTime(DateUtil.format(rs.getDate(4), "yyyy-MM-dd"));
					tableList.add(table);
				}
				if (rs != null) {
					rs.close();
				}
				// 释放资源(注意：关闭资源顺序 先打开后关闭) 完成 后关闭
				if (stmt != null) {
					try {
						stmt.close();
					} catch (SQLException e) {
						log.error("关闭Statement异常：{}", e);
					}
				}

			}
		} catch (SQLException e) {
			throw new BusinessException("execute sql occer error", e);
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e) {
				throw new BusinessException("关闭Connection异常：{}", e);
			}
		}
		return tableList;
	}
	/**
	 * 查询表
	 * @param dbConfig
	 * @param tableName
	 * @return
	 */
	public Map<String, String> queryTable(DatabaseEntity dbConfig,String tableName) {
		Map result = Maps.newHashMapWithExpectedSize(4);
		Connection conn = getConnection(dbConfig);
		try {
			// 执行sql语句,获取表名和表注释，在information_schema库下的表tables中的table_name为表名、TABLE_COMMENT为表注释信息
			String strSql = "";
			if (dbConfig.getDbType().equals("MySQL")) {
				strSql = "select table_name tableName, engine, table_comment tableComment, create_time createTime from information_schema.tables " +
						 "where table_schema = '"+dbConfig.getDbSchema()+"' and table_name = ?";
			} else if (dbConfig.getDbType().equals("PostgreSQL")) {
				strSql = "select t1.tablename as tableName,'' engine, obj_description(relfilenode, 'pg_class') as tableComment, now() as createTime from pg_tables t1, pg_class t2,pg_namespace t3 " +
						 "where t1.schemaname ='"+dbConfig.getDbSchema()+"'and t1.tablename = ? and t1.tablename = t2.relname and t1.schemaname = t3.nspname and t3.oid = t2.relnamespace ";
			} else if (dbConfig.getDbType().equals("Oracle")) {
				strSql = "select dt.table_name tableName,'' engine,dtc.comments tableComment,dt.last_analyzed createTime from user_tables dt,user_tab_comments dtc where dt.table_name=dtc.table_name and dt.table_name = upper(?)";
			} else if (dbConfig.getDbType().equals("SQLServer")) {
				strSql = "select * from (" +
						"select cast(so.name as varchar(500)) as tableName, 'mssql' as engine,cast(sep.value as varchar(500)) as tableComment, getDate() as createTime" +
						"from sysobjects so" +
						"left JOIN sys.extended_properties sep on sep.major_id=so.id and sep.minor_id=0" +
						"where (xtype='U' or xtype='v')" +
						") t where t.tableName= ?";
			}
			log.info("数据库表sql查询语句：{}" , strSql);
			// 获得sql的预编译对象
			PreparedStatement stmt = conn.prepareStatement(strSql);
			stmt.setString(1, tableName);
			// 执行查询
			ResultSet rs = stmt.executeQuery();
			// 展开结果集数据库
			while (rs.next()) {
				result.put("tableName",rs.getString(1));
				result.put("engine",rs.getString(2));
				result.put("tableComment",rs.getString(3));
				result.put("createTime",rs.getDate(4));
			}
			if(rs != null){
				rs.close();
			}
			// 释放资源(注意：关闭资源顺序 先打开后关闭) 完成 后关闭
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					log.error("关闭Statement异常：{}", e);
				}
			}
		} catch (SQLException e) {
			throw new BusinessException("execute sql occer error", e);
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				throw new BusinessException("关闭Connection异常：{}", e);
			}
		}
		return result;
	}

	/**
	 * 查询字段
	 * @param dbConfig
	 * @param tableName
	 * @return
	 */
	public List<Map<String, String>> queryColumns(DatabaseEntity dbConfig,String tableName) {
		List<Map<String, String>> list = Lists.newArrayListWithCapacity(8);
		Connection conn = getConnection(dbConfig);
		try {
			String strSql = "";
			// 根据表名【table_name】和库名【table_schema】获取表注释信息，在information_schema库下的表tables中的TABLE_COMMENT为表注释信息
			if (dbConfig.getDbType().equals("MySQL")) {
				strSql = "select column_name columnName, data_type dataType,'' as numLength,'' as numBit, column_comment columnComment, extra,column_key columnKey from information_schema.columns " +
						 "where table_name = ? and table_schema = '"+dbConfig.getDbSchema()+"'";
			} else if (dbConfig.getDbType().equals("PostgreSQL")) {
				strSql = "select t2.attname as columnName, pg_type.typname as dataType, '' as numLength,'' as numBit,col_description(t2.attrelid,t2.attnum) as columnComment, '' as extra," +
						 "(CASE t3.contype WHEN 'p' THEN 'PRI' ELSE '' END) as columnKey " +
						 "from pg_class as t1, pg_attribute as t2 inner join pg_type on pg_type.oid = t2.atttypid " +
						 "left join pg_constraint t3 on t2.attnum = t3.conkey[1] and t2.attrelid = t3.conrelid " +
						 "where t1.relname = ? and t2.attrelid = t1.oid and t2.attnum>0";
			}else if (dbConfig.getDbType().equals("Oracle")) {
				strSql = "select temp.column_name columnname," +
						 "temp.data_type dataType," +
						 "temp.DATA_PRECISION numLength," +
						 "temp.DATA_SCALE numBit," +
						 "temp.comments columnComment," +
						 "case temp.constraint_type when 'P' then 'PRI' when 'C' then 'UNI' else '' end columnKey," +
						 "'' extra from (" +
						 "select col.column_id,col.column_name," +
						 "col.data_type,col.DATA_PRECISION,col.DATA_SCALE," +
						 "colc.comments," +
						 "uc.constraint_type," +
						 "row_number() over (partition by col.column_name order by uc.constraint_type desc) as row_flg " +
						 "from user_tab_columns col " +
						 "left join user_col_comments colc " +
						 "on colc.table_name = col.table_name " +
						 "and colc.column_name = col.column_name " +
						 "left join user_cons_columns ucc " +
						 "on ucc.table_name = col.table_name " +
						 "and ucc.column_name = col.column_name " +
						 "left join user_constraints uc " +
						 "on uc.constraint_name = ucc.constraint_name " +
						 "where col.table_name = upper(?) " +
						 ") temp " +
						 "where temp.row_flg = 1 " +
						 "order by temp.column_id";
			}else if (dbConfig.getDbType().equals("SQLServer")) {
				strSql = "SELECT " +
						"cast(b.NAME AS VARCHAR(500)) AS columnName, '' as numLength,'' as numBit," +
						"cast(sys.types.NAME AS VARCHAR(500)) AS dataType," +
						"cast(c.VALUE AS VARCHAR(500)) AS columnComment,'' as extra," +
						"(SELECT CASE count( 1 ) WHEN 1 then 'PRI' ELSE '' END " +
						"FROM syscolumns,sysobjects,sysindexes,sysindexkeys,systypes " +
						"WHERE syscolumns.xusertype = systypes.xusertype " +
						"AND syscolumns.id = object_id(A.NAME) " +
						"AND sysobjects.xtype = 'PK' " +
						"AND sysobjects.parent_obj = syscolumns.id " +
						"AND sysindexes.id = syscolumns.id " +
						"AND sysobjects.NAME = sysindexes.NAME " +
						"AND sysindexkeys.id = syscolumns.id " +
						"AND sysindexkeys.indid = sysindexes.indid " +
						"AND syscolumns.colid = sysindexkeys.colid " +
						"AND syscolumns.NAME = B.NAME " +
						") as columnKey FROM " +
						"(select name,object_id from sys.tables " +
						"UNION all select name,object_id from sys.views ) a " +
						"INNER JOIN sys.COLUMNS b ON b.object_id = a.object_id " +
						"LEFT JOIN sys.types ON b.user_type_id = sys.types.user_type_id " +
						"LEFT JOIN sys.extended_properties c ON c.major_id = b.object_id " +
						"AND c.minor_id = b.column_id " +
						"WHERE a.NAME = ? and sys.types.NAME != 'sysname'";
			}
            log.info("数据库列字段sql查询语句：{}" , strSql);
            PreparedStatement stmt = conn.prepareStatement(strSql);
			stmt.setString(1,tableName);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				Map map = Maps.newHashMapWithExpectedSize(5);
				map.put("columnName",rs.getString(1));
				map.put("dataType",rs.getString(2));
				map.put("numLength",rs.getString(3));
				map.put("numBit",rs.getString(4));
				map.put("columnComment",rs.getString(5));
				map.put("extra",rs.getString(6));
				map.put("columnKey",rs.getString(7));
				list.add(map);
			}
			if(rs != null){
				rs.close();
			}
			// 关闭资源
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					log.error("关闭Statement异常：{}", e);
				}
			}
		} catch (SQLException e) {
			throw new BusinessException("execute sql occer error", e);
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				throw new BusinessException("关闭Connection异常：{}", e);
			}
		}

		return list;
	}


	public byte[] generatorCode(String[] tableNames,
								Long databaseId,
								String parentProjectName,
								String projectName,
								String packageName,
								String moduleName,
								String checkValues) {
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		ZipOutputStream zip = new ZipOutputStream(outputStream);
		Map param = Maps.newHashMapWithExpectedSize(1);
		param.put("id",databaseId);
		List<DatabaseEntity> databases = databaseDao.listDatabases(param);
		DatabaseEntity dbConfig = null;
		Set fileSet = Sets.newLinkedHashSetWithExpectedSize(10);
        if(CollectionUtil.isNotEmpty(databases) && databases.size() == 1){
			dbConfig = databases.get(0);
		}
		for(String tableName : tableNames){
			//查询表信息
			Map<String, String> table = queryTable(dbConfig,tableName);
			//查询列信息
			List<Map<String, String>> columns = queryColumns(dbConfig,tableName);
			//生成代码
			GenUtils.generatorCode(table, columns, zip,parentProjectName,projectName,packageName,moduleName,checkValues,dbConfig,fileSet);
		}
		IOUtils.closeQuietly(zip);
		return outputStream.toByteArray();
	}
	/**
	 * 连接数据库
	 *
	 * @param dbConfig:数据库连接配置信息
	 * @return: java.sql.Connection
	 */
	private Connection getConnection(DatabaseEntity dbConfig) {
		//创建用于连接数据库的Connection对象
		Connection con = null;
		try {
			// 加载JDBC驱动
			Class.forName(dbConfig.getDriver());
			// 根据数据库类型【mysql or oracle】，获取连接，与数据库建立连接
			con = DriverManager.getConnection(dbConfig.getUrl(), dbConfig.getUserName(), dbConfig.getPassword());
			DriverManager.setLoginTimeout(100);
		} catch (Exception e) {
			log.error("数据库连接失败:{}" , e);
		}
		//返回所建立的数据库连接信息
		return con;
	}

	public byte[] generatorCodes(String[] tableNamesStr, String parentProjectName, String projectName, String packageName, String moduleName, String checkValues) {
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		ZipOutputStream zip = new ZipOutputStream(outputStream);
		Map param = Maps.newHashMapWithExpectedSize(1);
		List<Integer> ids = Lists.newArrayListWithExpectedSize(3);
		for (String tableNameStr : tableNamesStr) {
			String[] tableNames = tableNameStr.split(",");
			ids.add(Integer.parseInt(tableNames[0]));
		}
		param.put("ids", ids);
		Set fileSet = Sets.newLinkedHashSetWithExpectedSize(10);
		List<DatabaseEntity> databases = databaseDao.listDatabases(param);
		//所选数据库循环
		int ds = 0;
		for (String tableNameStr : tableNamesStr) {
			ds++;
			String[] tableNames = tableNameStr.split(",");
			//数据库id
			Integer databaseId = Integer.parseInt(tableNames[0]);
			param.put("id",databaseId);
			List<DatabaseEntity> collect = databases.stream().filter(databaseEntity -> databaseId.equals(databaseEntity.getId())).collect(Collectors.toList());
			DatabaseEntity dbConfig = null;
			if(CollectionUtil.isNotEmpty(collect) && collect.size() == 1){
				dbConfig = collect.get(0);
			}
			int index = tableNameStr.indexOf(",");
			//数据库表
			String tables = tableNameStr.substring(index+1);
			for(String tableName : tables.split(",")){
				//查询表信息
				Map<String, String> table = queryTable(dbConfig,tableName);
				//查询列信息
				List<Map<String, String>> columns = queryColumns(dbConfig,tableName);
				//生成代码
				GenUtils.generatorCodes(databases,table, columns, zip,parentProjectName,projectName,packageName,moduleName,checkValues,dbConfig,fileSet,ds);
			}
		}
		IOUtils.closeQuietly(zip);
		return outputStream.toByteArray();
	}
}
